﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using WMSSyncData.Model;
using WMSSyncData.Model.WMSModel;
using WMSSyncData.Utils;

namespace WMSSyncData.DAL.Oracle
{
    public class Tddev111DAL : BaseOracleDAL<WMS_T_OutStock>
    {
        public List<WMS_T_OutStock> GetAll()
        {
            var sql = @"SELECT rawtohex(sys_guid()) guid,'B0101'||to_char(sysdate, 'yyMMdd')||LPAD (SEQ_WMS_M_outStock.NEXTVAL,5,'0') AS BillNo,temp.* FROM ( SELECT DISTINCT CASE 
	WHEN  t$qoor<0 THEN
	    '销售退货'
	    when substr(t$orno,0,3)='VMI' THEN
		'寄售出库'
		when substr(t$orno,0,3)='VMB' THEN
		'寄售订单'
		ELSE
		'销售订单'
END  BillType,t$orno AS RelatedBill,'0' AS status,t$bpid AS CustomerSN,t$lin1 as CustomerName,t$nama CustomerAddress,'admin' AS creator,SYSDATE as createTime,
t$cwar as Storage_SN,t$cwar as Ext3,t$sqnb as SN,t$pono as RowNo,t$stco as Ext1,t$prdt as Remark  
FROM TTDDEV111101 WHERE t$host=1 and 
substr(t$orno,0,3)!='HNR' and substr(t$orno,0,3)!='HRT' and substr(t$orno,0,3)!='HVM' and 
substr(t$orno,0,3)!='QNR' and substr(t$orno,0,3)!='QRT' and substr(t$orno,0,3)!='QVM') temp ";

            var dt = OracleDBHelper.GetDataSet(sql).Tables[0];

            var result = MakeTablePackage(dt);
            return result;
        }

        public DataTable GetChangeDT()
        {
            var sql = @"SELECT DISTINCT t$orno AS RelatedBill,t$sqnb as SN,t$pono as RowNo,t$status AS status,t$item as ProductNumber,t$qoor as PlanQty,t$prdt as Remark,t$text as Text FROM TTDDEV111101 WHERE t$status IN(2,3)";

            var dt = OracleDBHelper.GetDataSet(sql).Tables[0];

            return dt;

        }

        public int UpdateStatus(DataTable dt)
        {
            StringBuilder sb = new StringBuilder();
            //sb.AppendLine("begin");
            foreach (DataRow dr in dt.Rows)
            {
                var text = dr["Text"].ToString();
                if (!text.Contains("WMS已处理"))
                {
                    text = text + ";WMS已处理";
                }
                var sql = "UPDATE TTDDEV111101 set  t$status=1,t$text='" + text + "' where  t$pono='" + dr["RowNo"].ToString() + "' and t$orno='" + dr["RelatedBill"].ToString() + "' and t$sqnb='" + dr["SN"].ToString() + "'";
                sb.AppendLine(sql);
            }
            //sb.AppendLine("end;");
            var resultSql = sb.ToString().Trim();
            var result = OracleDBHelper.ExecuteNonQuery(resultSql);
            return result;
        }

        public int UpdateText(DataTable dt)
        {
            StringBuilder sb = new StringBuilder();
            //sb.AppendLine("begin");
            foreach (DataRow dr in dt.Rows)
            {
                var text = dr["Text"].ToString();
                if (!text.Contains("WMS无法处理"))
                {
                    text = text + ";WMS无法处理";
                }
                var sql = "UPDATE TTDDEV111101 set  t$Status=4,t$wdte1=SYSDATE,t$text='" + text + "'  where  t$pono='" + dr["RowNo"].ToString() + "' and t$orno='" + dr["RelatedBill"].ToString() + "' and t$sqnb='" + dr["SN"].ToString() + "'";
                sb.AppendLine(sql);
            }
            //sb.AppendLine("end;");
            var resultSql = sb.ToString().Trim();
            var result = OracleDBHelper.ExecuteNonQuery(resultSql);
            return result;
        }

        public List<WMS_T_OutStock> GetSPFData()
        {
            var sql = @"SELECT rawtohex(sys_guid()) as GUID,temp.* FROM(
SELECT DISTINCT BillNO,RelatedbillNo as RelatedBill,RowNo,CustomerSN,ToAddress as CustomerAddress, billtype,1 AS status,creator as Creator,createtime as createtime,nvl(OutWarehouse,' ') as Storage_SN,EXT1,EXT2,EXT3 FROM WMS_M_OUTSTOCK WHERE Data_Status=1 and billtype='半成品出库')temp";

            var dt = OracleDBHelper.GetDataSet(sql).Tables[0];

            var result = MakeTablePackage(dt);
            return result;
        }
    }
}
